/**
 * 
 */
package dataPersistence.dao;

import hitExceptions.InvalidDataException;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import common.Result;
import common.ValueResult;

import coreModel.*;

import dataPersistence.TransactionManager;
import dataPersistence.dto.ProductContainerDTO;
import dataPersistence.dto.ProductDTO;
import dataPersistence.dto.ProductGroupDTO;
import dataPersistence.dto.StorageUnitDTO;

/**
 * @author fantastic4
 *
 */
public class Product_DB_DAO implements IProductDAO
{
	private ProductDTO dto;



	/* (non-Javadoc)
	 * @see persistence.DataAccessObject#add(java.lang.Object)
	 */
	@Override
	public Result add(ProductDTO dto) 
	{
		Result result = new Result();
		try 
		{
			String query = "insert into Product (barcode, entryDate,description,shelfLife,threeMonthSupply,productSupply,productSupplyUnit) values (?, ?, ?, ?, ?, ?, ?)";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, dto.getBarcode());
			stmt.setDate(2, new Date(dto.getEntryDate().getTime())); // covert from util.Date to sql.Date
			stmt.setString(3, dto.getDescription());
			stmt.setInt(4, dto.getShelfLife());
			stmt.setInt(5, dto.getThreeMonthSupply());
			stmt.setFloat(6, dto.getProductSupply());
			stmt.setString(7, dto.getProductSupplyUnit());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not insert product");

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage()+" in addProductDAO add");
		}
		return result;
	}

	/* (non-Javadoc)
	 * @see persistence.DataAccessObject#update(java.lang.Object)
	 */
	@Override
	public Result update(ProductDTO dto)
	{
		Result result = new Result();
		try 
		{
			String query = "update Product set entryDate=?,description=?,shelfLife=?,threeMonthSupply=?,productSupply=?,productSupplyUnit=? where barcode =?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(7, dto.getBarcode());
			stmt.setDate(1, new Date(dto.getEntryDate().getTime())); // covert from util.Date to sql.Date
			stmt.setString(2, dto.getDescription());
			stmt.setInt(3, dto.getShelfLife());
			stmt.setInt(4, dto.getThreeMonthSupply());
			stmt.setFloat(5, dto.getProductSupply());
			stmt.setString(6, dto.getProductSupplyUnit());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not update product");

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;
	}


	/* (non-Javadoc)
	 * @see persistence.DataAccessObject#delete(java.lang.Object)
	 */
	@Override
	public Result delete(ProductDTO dto)
	{
		Result result = new Result();
		try
		{
			String query = "delete from Product where barcode =?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, dto.getBarcode());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not delete product");
		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;

	}

	/* (non-Javadoc)
	 * @see dataPersistence.dao.IProductDAO#getAllProducts()
	 */
	@Override
	public ValueResult<Set<ProductDTO>> getAllProducts()
	{
		ValueResult<Set<ProductDTO>> result = new ValueResult<Set<ProductDTO>>();

		PreparedStatement stmt = null;
		ResultSet rs=null;
		Set<ProductDTO> allProducts = new HashSet<ProductDTO>();
		try 
		{
			String query = "select barcode, entryDate,description,shelfLife,threeMonthSupply,productSupply,productSupplyUnit from Product";
			stmt = TransactionManager.getConnection().prepareStatement(query);

			rs = stmt.executeQuery();
			while (rs.next())
			{
				ProductDTO tmp = new ProductDTO();
				tmp.setBarcode(rs.getString(1));
				tmp.setEntryDate(rs.getDate(2)); 
				tmp.setDescription(rs.getString(3));
				tmp.setShelfLife(rs.getInt(4));
				tmp.setThreeMonthSupply(rs.getInt(5));
				tmp.setProductSupply(rs.getFloat(6));
				tmp.setProductSupplyUnit(rs.getString(7));
				allProducts.add(tmp);
			}
			result.setValue(allProducts);
			result.setStatus(true);

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}		
		finally {
			TransactionManager.safeClose(rs);
			TransactionManager.safeClose(stmt);
		}

		return result;		

	}

	public static void main(String[] var)
	{
		try {
			Product p = new Product(new Barcode("2nd new barcode"),"this is my product",1,1,new Supply(1,Unit.GALLONS));

			TransactionManager.initialize();
			TransactionManager.begin();
			Product_DB_DAO dao = new Product_DB_DAO();
			dao.add(new ProductDTO(p));
			TransactionManager.end(true);
			java.util.Date date = new java.util.Date();
			java.sql.Date otherDate = new java.sql.Date(date.getTime());
			System.out.println(date+"  "+ otherDate);
			date= otherDate;
			
			System.out.println(date);
			java.util.Date newDate = new java.util.Date(date.getTime());
			System.out.println(newDate);

		} catch (InvalidDataException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see dataPersistence.dao.IProductDAO#addProductToContainer(dataPersistence.dto.ProductDTO, java.lang.String)
	 */
	@Override
	public Result addProductToContainer(ProductDTO product, String containerID)
	{
		Result result = new Result();
		try
		{
			String query = "insert into ProductContainerToProduct (container_ID,productBarcode) values (?, ?)";
			System.out.println(product.getBarcode()+"containerID"+containerID);
			System.out.println(query);

			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, containerID);
			stmt.setString(2, product.getBarcode()); // covert from util.Date to sql.Date

			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not insert product into container");

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage() + "in addProductToContainer method");
		}
		return result;
	}

	/* (non-Javadoc)
	 * @see dataPersistence.dao.IProductDAO#removeProductFromContainer(dataPersistence.dto.ProductDTO, java.lang.String)
	 */
	@Override
	public Result removeProductFromContainer(ProductDTO product, String containerID)
	{
		Result result = new Result();
		try
		{
			String query = "delete from ProductContainerToProduct where productBarcode =? AND container_ID=?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);

			stmt.setString(1, product.getBarcode());
			stmt.setString(2,containerID);

			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not insert product container to product");

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;
	}

	/* (non-Javadoc)
	 * @see dataPersistence.dao.IProductDAO#updateProductInContainer(dataPersistence.dto.ProductDTO, java.lang.String, java.lang.String)
	 */
	@Override
	public Result updateProductInContainer(ProductDTO product, String oldContainer, String newContainer)
	{
		Result result = new Result();
		try 
		{
			String query = "update ProductContainerToProduct set container_ID=? where productBarcode =? AND container_ID=?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);

			//set Querry
			stmt.setString(1, newContainer);
			stmt.setString(2, product.getBarcode());
			stmt.setString(3, oldContainer);

			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not insert product cotnainer to product");

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;
	}

	public ValueResult<HashMap<ProductContainerDTO, ArrayList<ProductDTO>>> getAllContainersByProducts()
	{
		ValueResult<HashMap<ProductContainerDTO,ArrayList<ProductDTO>>> result = 
			new ValueResult<HashMap<ProductContainerDTO,ArrayList<ProductDTO>>>();

		PreparedStatement stmt = null;
		ResultSet rs=null;

		HashMap<ProductContainerDTO, ArrayList<ProductDTO>> allProducts = 
			new HashMap<ProductContainerDTO, ArrayList<ProductDTO>>();
		try 
		{
			String query = "select pc.container_ID,pc.name, pc.parent,"+
			"pg.threeMonthSupply,pg.threeMonthSupplyUnit,"+
			"p.barcode, p.entryDate, p.description,p.shelfLife,"+
			"p.threeMonthSupply, p.productSupply,p.productSupplyUnit"+

			"FROM (ProductContainer AS pc LEFT JOIN ProductGroup AS pg"+
			"ON (pc.container_ID=pg.productContainer_ID) " +
			"JOIN ProductContainerToProduct AS pctc"+
			"ON(pc.container_ID=pctc.container_ID)  JOIN Product AS p"+
			"ON(pctc.productBarcode=p.barcode))";

			stmt = TransactionManager.getConnection().prepareStatement(query);

			rs = stmt.executeQuery();
			while (rs.next())
			{
				ProductContainerDTO containerDTO;
				//create container
				if(rs.getString("threeMonthSupply")==null)
				{
					containerDTO=new StorageUnitDTO();
					((ProductGroupDTO) containerDTO).setThreeMonthUnit(rs.getString(5));
					((ProductGroupDTO) containerDTO).setThreeMonthsSize(rs.getFloat(4));
				}
				else
				{
					containerDTO=new ProductGroupDTO();
				}
				containerDTO.setName(rs.getString(2));
				containerDTO.setProductContainerID(rs.getString(1));

				//check to see if there is an entry for this container
				if(!allProducts.containsKey(containerDTO))
				{
					allProducts.put(containerDTO, new ArrayList<ProductDTO>());
				}

				//create productDTO
				ProductDTO tmp = new ProductDTO();
				tmp.setBarcode(rs.getString(6));
				tmp.setEntryDate(rs.getDate(7)); 
				tmp.setDescription(rs.getString(8));
				tmp.setShelfLife(rs.getInt(9));
				tmp.setThreeMonthSupply(rs.getInt(10));
				tmp.setProductSupply(rs.getFloat(11));
				tmp.setProductSupplyUnit(rs.getString(12));

				//add to map
				allProducts.get(containerDTO).add(tmp);
			}
			result.setValue(allProducts);
			result.setStatus(true);

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}		
		finally {
			TransactionManager.safeClose(rs);
			TransactionManager.safeClose(stmt);
		}

		return result;		
	}

	@Override
	public Result removeProductFromSystem(ProductDTO dto)
	{	
		Result result = new Result();
		result=delete(dto);

		//false return and roll back
		if (!result.getStatus())
			return result;

		try
		{
			String query = "delete from ProductContainerToProduct where productBarcode =?";
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, dto.getBarcode());
			if (stmt.executeUpdate() == 1)
				result.setStatus(true);
			else
				result.setMessage("Could not delete product container to container");
		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}
		return result;

	}

	public ValueResult<ArrayList<String>> getAllParentsOfProduct(String productBarcode)
	{


		ValueResult<ArrayList<String>> result = new ValueResult<ArrayList<String>>();

		PreparedStatement stmt = null;
		ResultSet rs=null;
		ArrayList<String> allContainer = new ArrayList<String>();
		try 
		{
			String query = "select container_ID from ProductContainerToProduct where productBarcode = ?";
			stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, productBarcode);
			rs = stmt.executeQuery();
			while (rs.next())
			{
				allContainer.add(rs.getString(1));
			}
			result.setValue(allContainer);
			result.setStatus(true);

		}
		catch (SQLException e) 
		{
			result.setMessage(e.getMessage());
		}		
		finally {
			TransactionManager.safeClose(rs);
			TransactionManager.safeClose(stmt);
		}

		return result;		

	}
}
